package tddd24.ajgallery.server;

import tddd24.ajgallery.shared.FieldVerifier;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.GregorianCalendar;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.filefilter.DirectoryFileFilter;
import org.apache.commons.io.filefilter.RegexFileFilter;

/**
 * The server side implementation of the RPC service.
 */

public class DatabaseController {

	private Connection conn;
	private Statement stat;
	private PreparedStatement prep;
	private ResultSet rs;
	private String[] result = new String[3];
	private java.util.Date date;
	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 

	public DatabaseController(String dbPath)
	{
		try {
			Class.forName("SQLite.JDBCDriver");        // SQLite	   
			conn =  DriverManager.getConnection("jdbc:sqlite:/" + dbPath + "/image.db");    // SQLite DB

			prep = conn.prepareStatement("insert into gallery (fileName, date) values (?, ?);");
			stat = conn.createStatement();

			stat.executeUpdate("drop table if exists gallery;");

			// In SQLite "INTEGER PRIMARY KEY" automatically gets autoincrementation when null is inserted into the field.
			stat.executeUpdate("create table gallery (fileNumber INTEGER PRIMARY KEY, fileName VARCHAR(300), date TIMESTAMP);");

		} catch (Exception e) {
			e.printStackTrace();		    	  
		}
	}

	public void finalize() throws SQLException{
		rs.close();
		conn.close();
	}

	/** Adds targetFile to the database, if the filename does not already exist there.
	* @return	True if targetFile was added successfully. False if targetFile's filename already exists in the database.
	 */
	public boolean addFile(File targetFile) throws SQLException{

		rs = stat.executeQuery("select * from gallery where fileName='" + targetFile.getName() + "';");		

		if(! rs.first())
		{	
			date = new java.util.Date();

			// fileNumber autoincrements so only two fields necessary.
			prep.setString(1, targetFile.getName());					
			prep.setString(2, dateFormat.format(date));

			prep.execute();

			return true;
		}
		else
			return false;
	}

	/** Adds existing files in the targetDirectory to the database, if they are not already added.*/
	public void addExistingFiles(File targetDirectory) {

		Collection<File> files = FileUtils.listFiles(targetDirectory, new RegexFileFilter("^(.*?)"), 
				DirectoryFileFilter.DIRECTORY);

		try{
			for(File currentFile : files) {
				if(! currentFile.getName().equals("Thumbs.db"))
					addFile(currentFile);
			}
		}
		catch (Exception e) {
			e.printStackTrace();	
		}
	}

	/** Gets the data associated with the fileNumber in the database.
	 * 
	 * @return A String[3] array. The first field is the fileNumber, the second field is the fileName 
	 * and the third field is the upload date for the file. The function returns empty strings if the 
	 * fileNumber does not exist in the database.
	 */
	public String[] getFileData(long fileNumber){
		try{
			rs = stat.executeQuery("SELECT * FROM gallery WHERE fileNumber='" + fileNumber + "';");

			if(rs.first())
			{
				result[0] = rs.getString("fileNumber");
				result[1] = rs.getString("fileName");
				result[2] = rs.getString("date");
			}
			else
			{
				result[0] = "";
				result[1] = "";
				result[2] = "";
			}

		} catch (Exception e) {
			e.printStackTrace();	
		}
		return result;	
	}
	
	/** Returns the first (and therefore the lowest) fileNumber in the database.
	 * 
	 * @return	The first fileNumber in the database or -1 if there are no entries in the database.
	 */
	public long getFirstFileNumber() {
		
		try {
			rs = stat.executeQuery("SELECT MIN(fileNumber) FROM gallery;");
			
			if(rs.next())
				return rs.getLong(1);
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return -1;
	}

	/** Returns the last (and therefore the highest) fileNumber in the database.
	 * 
	 * @return	The last fileNumber in the database or -1 if there are no entries in the database.
	 */
	public long getLastFileNumber() {
		
		try {
			// rs = stat.executeQuery("SELECT last_insert_rowid() FROM gallery;");
			rs = stat.executeQuery("SELECT MAX(fileNumber) FROM gallery;");
			
			if(rs.next())
				return rs.getLong(1);
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return -1;
	}
	
	/** Returns the next fileNumber after currentFilenumber in the database.
	 * 
	 * @return	The next fileNumber after currentFilenumber in the database 
	 * or -1 if there are no entries in the database or the end of the database has been reached.
	 */
	public long getNextFileNumber(long currentFilenumber) {
		
		try {
			rs = stat.executeQuery("SELECT fileNumber FROM gallery WHERE fileNumber > " 
					+ currentFilenumber + " ORDER BY fileNumber ASC;");
			
			if(rs.next())
				return rs.getLong(1);
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return -1;
	}
	
	/** Returns the fileNumber before currentFilenumber in the database. 
	 * 
	 * @return	The fileNumber before currentFilenumber in the database 
	 * or -1 if there are no entries in the database or the end of the database has been reached.
	 */
	public long getPrecedingFileNumber(long currentFilenumber) {
		
		try {
			rs = stat.executeQuery("SELECT fileNumber FROM gallery WHERE fileNumber < " 
					+ currentFilenumber + " ORDER BY fileNumber DESC;");
			
			if(rs.next())
				return rs.getLong(1);
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return -1;
	}
}


